﻿/******************************************************************************
'Description:装试周计划看板
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yuxj
'create Date:2019.01.08 
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.IO;
namespace ReportBusiness
{
    public class ZSWeekPlanLookBoardBusiness
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();

        //查询发动机周计划 add zhunan 2020.6.19
        public DataTable GetMachineWeekPlan(Dictionary<string, string> para)
        {
            //因为ERP只推1次料，只查询第一次部、传、总大阶段（AND ws.sequence <= 8）
            //刘飞沟通确认过 2020.6.18
            string sql = @" SELECT distinct u.attributevalue,m.mfgorderid 
                            FROM mfgorder m 
                            LEFT JOIN ordertype ot ON m.ordertypeid=ot.ordertypeid
                            LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid
                            LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid
                            LEFT JOIN product p ON NVL(pb.revofrcdid,m.productid)=p.productid
                            LEFT JOIN enginepartplan ep ON ep.mfgorderid=m.mfgorderid and ep.productid=p.productid
                            LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                            WHERE ep.workflowstepid IS NOT NULL AND u.userattributename='发动机' AND ot.ordertypename='PD01' ";

            if (para.ContainsKey("week"))
            {
                if (para["week"].ToString() == "thisWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 1) AND (trunc(sysdate, 'd') + 7) "; //本周一 ~ 本周日
                }
                else if (para["week"].ToString() == "prevWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') - 6) AND (trunc(sysdate, 'd') + 0) "; //上周一 ~ 上周日
                }
                else
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 8) AND (trunc(sysdate, 'd') + 14) "; //下周一 ~ 下周日
                }
            }

            //获取查到的发动机记录
            DataTable dtMachine = dal.GetDataTable(sql);

            string[] Day = new string[] { "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun" };
            for (int i = 0; i <= Day.Length - 1; i++)
            {
                dtMachine.Columns.Add(Day[i]);
            }

            if (dtMachine.Rows.Count == 0)
            {
                return dtMachine;
            }

            dal.ExecuteSql("alter session set NLS_DATE_LANGUAGE = American"); //转为英文
            //AND wfs.sequence <= 8 只查询第一次装配
            sql = @"SELECT ep.mfgorderid,wfs.workflowstepname,INITCAP(to_char(ep.plannedstartdate,'dy')) weekday,ep.usecompleted,ep.exceptiondesc
                    FROM enginepartplan ep
                    LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
                    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
                    WHERE ep.workflowstepid IS NOT NULL AND ep.productid = p.productid ";

            if (para.ContainsKey("week"))
            {
                if (para["week"].ToString() == "thisWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 1) AND (trunc(sysdate, 'd') + 7) "; //本周一 ~ 本周日
                }
                else if (para["week"].ToString() == "prevWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') - 6) AND (trunc(sysdate, 'd') + 0) "; //上周一 ~ 上周日
                }
                else
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 8) AND (trunc(sysdate, 'd') + 14) "; //下周一 ~ 下周日
                }
            }

            sql += @"  AND ep.mfgorderid IN (
                            SELECT distinct m.mfgorderid 
                            FROM mfgorder m 
                            LEFT JOIN ordertype ot ON m.ordertypeid=ot.ordertypeid
                            LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid
                            LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid
                            LEFT JOIN product p ON NVL(pb.revofrcdid,m.productid)=p.productid
                            LEFT JOIN enginepartplan ep ON ep.mfgorderid=m.mfgorderid and ep.productid=p.productid
                            LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                            WHERE ep.workflowstepid IS NOT NULL AND u.userattributename='发动机' AND ot.ordertypename='PD01' ";

            if (para.ContainsKey("week"))
            {
                if (para["week"].ToString() == "thisWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 1) AND (trunc(sysdate, 'd') + 7) "; //本周一 ~ 本周日
                }
                else if (para["week"].ToString() == "prevWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') - 6) AND (trunc(sysdate, 'd') + 0) "; //上周一 ~ 上周日
                }
                else
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 8) AND (trunc(sysdate, 'd') + 14) "; //下周一 ~ 下周日
                }
            }

            sql += " ) ";
            sql += " GROUP BY ep.mfgorderid,wfs.workflowstepname,INITCAP(to_char(ep.plannedstartdate,'dy')),ep.usecompleted,ep.exceptiondesc";
            sql += " ORDER BY ep.mfgorderid,wfs.workflowstepname";

            DataTable dtBase = dal.GetDataTable(sql);

            sql = @"SELECT t.mfgorderid,t.attributevalue,t.workflowstepname,t.productname,
                           CASE WHEN t.today < t.startdate AND t.isQitao = 1 THEN 'green'
                                WHEN t.today < t.startdate AND t.isQitao = 0 THEN 'transparent'
                                WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 1 THEN 'green'
                                WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 0 THEN 'yellow'
                                WHEN t.today > t.enddate AND t.isQitao = 1 THEN 'green'
                                WHEN t.today > t.enddate AND t.isQitao = 0 THEN 'red' END status
                    FROM
                    (
                        SELECT TRUNC((ep.plannedstartdate - NVL(ep.priordays,1))) startdate,trunc(ep.plannedstartdate) enddate,trunc(SYSDATE) today,
                               wfs.workflowstepname,u.attributevalue,
                               m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty,
                               SUM(pl.Qtyrequired) Qtyrequired,CASE WHEN cmm.qty IS NOT NULL THEN 1 ELSE 0 END isQitao
                        FROM enginepartplan ep
                        LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
                        LEFT JOIN mfgorder m ON m.mfgorderid = ep.mfgorderid
                        LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机'
                        LEFT JOIN bom b ON b.workflowid = ep.workflowid
                        LEFT JOIN productmateriallistitem PL ON pl.Bomid = b.bomid
                        LEFT JOIN productbase pb1 ON pb1.productbaseid = pl.Productbaseid
                        LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,pl.Productid)
                        LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                        LEFT JOIN ( SELECT cm.mfgordername,cm.productname,SUM(NVL(cm.qty,0)) qty 
                                    FROM Collecterpmaterial cm 
                                    WHERE cm.qty > 0 
                                    GROUP BY cm.mfgordername,cm.productname
                                  ) cmm ON cmm.mfgordername = m.mfgordername AND cmm.productname = pb.productname
                        WHERE pl.Bigsmallpart = '大零件'
                              AND u.attributevalue IN 
                              (
                                  SELECT distinct u.attributevalue
                                  FROM mfgorder m 
                                  LEFT JOIN ordertype ot ON m.ordertypeid=ot.ordertypeid
                                  LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid
                                  LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid
                                  LEFT JOIN product p ON NVL(pb.revofrcdid,m.productid)=p.productid
                                  LEFT JOIN enginepartplan ep ON ep.mfgorderid=m.mfgorderid and ep.productid=p.productid
                                  LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                                  WHERE ep.workflowstepid IS NOT NULL AND u.userattributename='发动机' AND ot.ordertypename='PD01' ";

            if (para.ContainsKey("week"))
            {
                if (para["week"].ToString() == "thisWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 1) AND (trunc(sysdate, 'd') + 7) "; //本周一 ~ 本周日
                }
                else if (para["week"].ToString() == "prevWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') - 6) AND (trunc(sysdate, 'd') + 0) "; //上周一 ~ 上周日
                }
                else
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 8) AND (trunc(sysdate, 'd') + 14) "; //下周一 ~ 下周日
                }
            }

            sql += @" )
                        GROUP BY ep.plannedstartdate,ep.priordays,wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty
                    ) t ";

            DataTable dtEngineProductErp = dal.GetDataTable(sql);

            //资料填充到dtMachine
            DataRow[] drEngineWorkflowstep;
            DataRow[] drss;
            string StepList = string.Empty;

            foreach (DataRow dr in dtMachine.Rows)
            {
                drEngineWorkflowstep = dtBase.Select(string.Format("mfgorderid='{0}'", dr["mfgorderid"].ToString()));
                foreach (DataColumn cName in dtMachine.Columns)
                {
                    string colName = cName.ColumnName.ToString();
                    if (colName == "ATTRIBUTEVALUE" || colName == "MFGORDERID")
                    {
                        continue;
                    }
                    drss = drEngineWorkflowstep.CopyToDataTable().Select(string.Format("weekday='{0}'", colName));
                    foreach (DataRow drr in drss)
                    {
                        string filter = string.Format("mfgorderid = '{0}' AND workflowstepname = '{1}'", drr["mfgorderid"].ToString(), drr["workflowstepname"].ToString());
                        if (drr["usecompleted"].ToString() == "1")
                        {
                            //大阶段若已完工，显示亮绿色
                            StepList += string.Format("{0}&{1}&{2};", drr["workflowstepname"].ToString(), "finished", drr["exceptiondesc"]);
                        }
                        else
                        {
                            if (drr["workflowstepname"].ToString() == "0090-部装" || drr["workflowstepname"].ToString() == "0100-传装" || drr["workflowstepname"].ToString() == "0110-总装")
                            {
                                //二次部装、传装、总装因为考虑ERP发料无意义，故显示透明色
                                StepList += string.Format("{0}&{1}&{2};", drr["workflowstepname"].ToString(), "transparent", drr["exceptiondesc"]);
                            }
                            else
                            {
                                if (dtEngineProductErp.Select(filter).Length > 0)
                                {
                                    if (dtEngineProductErp.Select(filter).CopyToDataTable().Select("status='red'").Length > 0)
                                    {
                                        //阶段各大零件中只要有一个是红色的，则整个大阶段都显示为红色
                                        StepList += string.Format("{0}&{1}&{2};", drr["workflowstepname"].ToString(), "red", drr["exceptiondesc"]);
                                    }
                                    else
                                    {
                                        if (dtEngineProductErp.Select(filter).CopyToDataTable().Select("status='yellow'").Length > 0)
                                        {
                                            //阶段各大零件中只要有一个是黄色的，则整个大阶段都显示为黄色
                                            StepList += string.Format("{0}&{1}&{2};", drr["workflowstepname"].ToString(), "yellow", drr["exceptiondesc"]);
                                        }
                                        else
                                        {
                                            if (dtEngineProductErp.Select(filter).CopyToDataTable().Select("status='green'").Length == dtEngineProductErp.Select(filter).Length)
                                            {
                                                //阶段所有大零件都为绿色时，则大阶段显示为绿色
                                                StepList += string.Format("{0}&{1}&{2};", drr["workflowstepname"].ToString(), "green", drr["exceptiondesc"]);
                                            }
                                            else
                                            {
                                                //阶段所有大零件全为透明色或有一部分是绿色，大阶段显示透明色
                                                StepList += string.Format("{0}&{1}&{2};", drr["workflowstepname"].ToString(), "transparent", drr["exceptiondesc"]);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    //不需要集件的大阶段，颜色显示透明色
                                    StepList += string.Format("{0}&{1}&{2};", drr["workflowstepname"].ToString(), "transparent", drr["exceptiondesc"]);
                                }
                            }
                        }
                    }

                    if (StepList.Length > 0)
                    {
                        StepList = StepList.Substring(0, StepList.Length - 1);
                    }

                    dr[colName] = StepList;
                    StepList = string.Empty;
                }

            }
            dtMachine.AcceptChanges();
            return dtMachine;
        }

        //查询发动机周计划明细 add zhunan 2020.6.19
        public DataTable GetMachineWeekPlanDetails(Dictionary<string, object> para)
        {
            string sql = @"
SELECT t.*,nvl(m.productname,'--') productname,nvl(m.status,'不集件') status,NVL(t.priordays,1) || '天' AS qiLiangDays
FROM
(
SELECT u.attributevalue,mfg.mfgordername,trunc(ep.plannedstartdate) plannedstartdate,wfs.workflowstepname,
       decode(ep.usecompleted,'1','已完工','0','未完工') usecompleted ,max(ep.finishdate) finishdate,TRUNC(SYSDATE) today,NVL(ep.priordays,1) priordays
FROM enginepartplan ep
LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename = '发动机'
WHERE ep.workflowstepid IS NOT NULL AND ep.productid = p.productid ";
            if (para.ContainsKey("week"))
            {
                if (para["week"].ToString() == "thisWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 1) AND (trunc(sysdate, 'd') + 7) "; //本周一 ~ 本周日
                }
                else if (para["week"].ToString() == "prevWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') - 6) AND (trunc(sysdate, 'd') + 0) "; //上周一 ~ 上周日
                }
                else
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 8) AND (trunc(sysdate, 'd') + 14) "; //下周一 ~ 下周日
                }
            }
            sql += @"
      AND ep.mfgorderid IN 
      (
        SELECT distinct m.mfgorderid 
        FROM mfgorder m 
        LEFT JOIN ordertype ot ON m.ordertypeid=ot.ordertypeid
        LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid
        LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid
        LEFT JOIN product p ON NVL(pb.revofrcdid,m.productid)=p.productid
        LEFT JOIN enginepartplan ep ON ep.mfgorderid=m.mfgorderid and ep.productid=p.productid
        LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
        WHERE ep.workflowstepid IS NOT NULL AND u.userattributename='发动机' AND ot.ordertypename='PD01' ";

            if (para.ContainsKey("week"))
            {
                if (para["week"].ToString() == "thisWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 1) AND (trunc(sysdate, 'd') + 7) "; //本周一 ~ 本周日
                }
                else if (para["week"].ToString() == "prevWeek")
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') - 6) AND (trunc(sysdate, 'd') + 0) "; //上周一 ~ 上周日
                }
                else
                {
                    sql += " AND ep.plannedstartdate BETWEEN (trunc(sysdate, 'd') + 8) AND (trunc(sysdate, 'd') + 14) "; //下周一 ~ 下周日
                }
            }

            sql += string.Format(" ) AND u.attributevalue='{0}' AND wfs.workflowstepname = '{1}'", para["engineno"].ToString(), para["workflowstepname"].ToString());

            sql += @"
        GROUP BY u.attributevalue,mfg.mfgordername,ep.plannedstartdate,wfs.workflowstepname,ep.usecompleted,ep.priordays
) t
LEFT JOIN (
  SELECT t.mfgorderid,t.attributevalue,t.workflowstepname,t.productname,
         CASE WHEN t.usecompleted = 1 THEN '已发料'
              WHEN t.today < t.startdate AND t.isQitao = 1 THEN '已发料'
              WHEN t.today < t.startdate AND t.isQitao = 0 THEN '未到期量期'
              WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 1 THEN '已发料'
              WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 0 THEN '期量内未发料'
              WHEN t.today > t.enddate AND t.isQitao = 1 THEN '已发料'
              WHEN t.today > t.enddate AND t.isQitao = 0 THEN '超期未发料' 
         END status,
         t.usecompleted,t.sequence
  FROM
  (
      SELECT TRUNC((ep.plannedstartdate - 1)) startdate,trunc(ep.plannedstartdate) enddate,trunc(SYSDATE) today,
             wfs.workflowstepname,u.attributevalue,
             m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty,
             SUM(pl.Qtyrequired) Qtyrequired,CASE WHEN cmm.qty IS NOT NULL THEN 1 ELSE 0 END isQitao,ep.usecompleted,wfs.sequence
      FROM enginepartplan ep
      LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
      LEFT JOIN mfgorder m ON m.mfgorderid = ep.mfgorderid
      LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机'
      LEFT JOIN containerinstore CS ON cs.Aufnr = m.mfgordername
      LEFT JOIN bom b ON b.workflowid = ep.workflowid
      LEFT JOIN productmateriallistitem PL ON pl.Bomid = b.bomid
      LEFT JOIN productbase pb1 ON pb1.productbaseid = pl.Productbaseid
      LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,pl.Productid)
      LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
      LEFT JOIN ( SELECT cm.mfgordername,cm.productname,SUM(NVL(cm.qty,0)) qty 
                  FROM Collecterpmaterial cm 
                  WHERE cm.qty > 0 
                  GROUP BY cm.mfgordername,cm.productname
                ) cmm ON cmm.mfgordername = m.mfgordername AND cmm.productname = pb.productname
      WHERE pl.Bigsmallpart = '大零件' ";

            sql += string.Format(" AND u.attributevalue='{0}' AND wfs.workflowstepname = '{1}'", para["engineno"].ToString(), para["workflowstepname"].ToString());

            sql += @"
      GROUP BY ep.plannedstartdate,wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty,ep.usecompleted,wfs.sequence
  ) t 
) m ON m.attributevalue = t.attributevalue AND m.workflowstepname = t.workflowstepname AND t.workflowstepname NOT IN ('0090-部装','0100-传装','0110-总装') ";
            return dal.GetDataTable(sql);
        }
    }
}
